//
//  LLSQLiteManager.swift
//  新印象
//
//  Created by wyl on 2016/12/28.
//  Copyright © 2016年 魏延龙. All rights reserved.
//

import UIKit
import SQLite

let path = LLFileManagerTool.sharemanager.creatFile(path: "sql")

enum dataType : String{
    case integer,un_integer,text,un_text
}

class LLSQLiteManager: NSObject {
    
    static let shareManager = LLSQLiteManager()
    let db = try? Connection("\(path)/db.sqlite")
    var datatype : dataType?
    
    func creatSQL(tableName : String ,model : sqlData) {
        print(path)
        let users = Table(tableName)
        let creat = users.create { (t) in
            let mirr = Mirror(reflecting: model)
            let id = Expression<Int64>("id")
            t.column(id, primaryKey: true)
            for case let (label?,_) in mirr.children {
                let type = getValueByKey(obj: model, key: label) as! dataType
                switch type {
                case .integer:
                    let plat = Expression<Int?>(label)
                    t.column(plat)
                case .text:
                    let plat = Expression<String?>(label)
                    t.column(plat)
                case .un_integer:
                    let plat = Expression<Int>(label)
                    t.column(plat)
                case .un_text:
                    let plat = Expression<String>(label)
                    t.column(plat)
                }
            }
        }
        _ = try? db?.run(creat)
    }
    
    func insertSQL(tableName : String ,model : sqlData) {
        let users = Table(tableName)
        let mirr = Mirror(reflecting: model)
        var setters = Array<Setter>()
        for case let (label?,value) in mirr.children {
            let type = getValueByKey(obj: model, key: label) as! dataType
            switch type {
            case .integer:
                let plat = Expression<Int?>(label)
                let set = plat <- (value as! Int)
                setters.append(set)
            case .text:
                let plat = Expression<String?>(label)
                let set = plat <- String(describing: value)
                setters.append(set)
            case .un_integer:
                let plat = Expression<Int>(label)
                let set = plat <- value as! Int
                setters.append(set)
            case .un_text:
                let plat = Expression<String>(label)
                let set = plat <- String(describing: value)
                setters.append(set)
            }
        }
        let insert = users.insert(setters)
        _ = try? db?.run(insert)
    }
    
    func queryAllSQL(tableName : String ,model : sqlData) -> Array<Dictionary<String, Any>> {
        let users = Table(tableName)
        let pre = try? (db?.prepare(users))!
        var arr = Array<Dictionary<String, Any>>()
        for user in pre! {
            let mirr = Mirror(reflecting: model)
            var tempDic = Dictionary<String, Any>()
            for case let (label?,_) in mirr.children {
                let type = getValueByKey(obj: model, key: label) as! dataType
                switch type {
                case .integer:
                    let plat = Expression<Int?>(label)
                    tempDic[label] = user[plat]
                case .text:
                    let plat = Expression<String?>(label)
                    tempDic[label] = user[plat]
                case .un_integer:
                    let plat = Expression<Int>(label)
                    tempDic[label] = user[plat]
                case .un_text:
                    let plat = Expression<String>(label)
                    tempDic[label] = user[plat]
                }
            }
            arr.append(tempDic)
        }
        return arr
    }
    
    func test(tableName : String) {
        let users = Table(tableName)
        let plat = Expression<Int?>("id")
        let alice = users.filter(plat == 12)
//        db.run (alice.select([Expressible]))
    }
    
    private func getValueByKey(obj:AnyObject, key: String) -> Any {
        let hMirror = Mirror(reflecting: obj)
        
        for case let (label?, value) in hMirror.children {
            if label == key {
                return unwrap(any: value)
            }
        }
        return NSNull()
    }
    
    private func unwrap(any:Any) -> dataType {
        let mi = Mirror(reflecting: any)
        let subject = mi.subjectType
        if subject == Swift.ImplicitlyUnwrappedOptional<Swift.Int>{
            datatype = .un_integer
        } else if (subject == Swift.ImplicitlyUnwrappedOptional<Swift.String>){
            datatype = .un_text
        } else if (subject == Swift.Optional<Swift.Int>){
            datatype = .integer
        } else if (subject == Swift.Optional<Swift.String>){
            datatype = .text
        }
        return datatype!
    }
    
    
}




class sqlData: NSObject {
//    var id : Int!
    var name : String?
    var email : String!
    var age : Int!
    var tel : Int?
    
}

